In typical database systems, users store, update and retrieve information by submitting commands to a database application. To be correctly processed, the commands must comply with the database language that is supported by the database application. One popular database language is known as Structured Query Language (SQL).
A logical unit of work that is comprised of one or more database language statements is referred to as a transaction. When multiple users share a database system, it is impractical to allow only one transaction to execute at a time. However, when many transactions are allowed to execute at the same time, it is possible for the transactions to interact in undesirable ways.
Some database systems provide a buffer cache that is shared among the processes that are executing transactions in a database. The buffer cache is a pool of dynamic memory that is used for temporarily storing blocks of data. All processes are allowed to read and store data to the buffer cache. However, to prevent undesirable interaction between processes, only one process or special group of processes, referred to herein as the "writer process", writes updated data from the buffer cache to the database.
Some transactions require sort operations to be performed on data from the database. Space in dynamic memory is required to perform such sort operations. The portion of memory allocated to a transaction to perform a sort operation is referred to as a sort area.
After a process performs a sort operation, the sorted data may have to be written to the database. Typically, the database is updated by writing the sorted data to the buffer cache, and allowing the writer process to store the sorted data from the buffer cache to the database. However, a single process for writing data to a disk represents a potential bottleneck for data throughput, especially where multiple, concurrent users of the database are performing operations that require data to be written to the database.
Consequently, some database systems allow transactions to write sorted data directly to a disk. During direct write sorts, sorted data is written to a set of write buffers that are not shared with other processes. The sorted data is written asynchronously from the write buffers directly to disk, thereby bypassing the buffer cache.
Database systems that allow processes to write sorted data directly to disk often allow a user to specify the number and size of the write buffers. The database system may provide program parameters which the user could set to specify the characteristics of the write buffers. For example, one parameter could be used to set the number of write buffers, and a second parameter could be used to set the size of each write buffer.
Under operating conditions where available memory space is abundant on the system, the direct write sort method is a quick and efficient way to perform database sort operations. Under these conditions, the buffer size and number parameters could be set to values which exceed the expected memory requirements for possible sort operations. However, under operating conditions where memory resources are limited and memory space is not readily available, allocating memory for large write buffers during sorts could result in a reduction of the overall performance of the database.
Based on the foregoing, it is clearly desirable to provide a database system that provides for automatic tuning so that the number and size of write buffers is allocated in accordance with the amount of memory available for sort operations and without the need for user programming of fixed tuning parameters.